黑色好看版 - 傳送門

正文開始

上一篇文章中，咱們有提到了兩種資料庫層的擴展方式 :

• 分庫
• 分表

其中分表是用來解決單表太大的問題，而接下來本章節要來介紹另一種處理單表太的工具 :

分區表

本篇文章分以下幾個章節 :

• 分區概念
• MySQL 分區的切分類型
• 分區使用的注意事項

分區概念

分區表的核心概念為 :

將一張大表，根據『 規則 』拆分為『 隱藏 』的小表

觀念和分表事實上完全相同，就差在『 隱藏 』這個字詞上。它們的差異如下

• 分表 : 分表後，應用層需要修改 sql 操作位置，指定到對應的分表上。
• 分區 : 分區後，應用層『 不 』需要修改 sql 操作位置，資料庫層會自動幫你處理。

也就是說假設是使用 type 這個欄位來『 分表 』那你在查詢時可能需為變成如下指令 :

user 根據 type 拆分成三個表 :
1. user_type_A 表
2. user_type_B 表
3. user_type_C 長

SELECT * FROM user_type_A WHERE name = 'mark';

而如果是『 分區表 』的話為 :

SELECT * FROM user WHERE name = 'mark';

這裡問個問題 ~ 那實務上要使用分表還是分區呢 ?

首先這兩個沒有互相排斥，兩個可以一起使用，但如果是要先選一個來用的話，要選那個呢 ? 目前我是覺得『 分區 』比較簡單實現，但是它在使用上會比較需要『 使用者 』注意，一個用不好，性能反而會下降。

而『 分表 』某些方面來說彈性較大，可以做的業務較多，但相對的除非有一個很完美的中間件團隊，不然坑如果直接各別讓『 使用者 』直接用資料庫，會天下大亂的。

~ 小知識 ~

分區表這個東西在不少資料庫上都有提供像 mysql、ms-sql、postgresql 等都有支援。

分表區的優點

分區有以下幾個好處 :

• 減少修改時對索引樹的維護 : 因為越大的樹越難維護，所以切小後，每顆樹就好維護多了。
• 減少索引碎片處理 : 只要是有進行修改，就有可能會讓索引樹一個洞一個洞的，而通常會下達指令來整理這顆樹，但是這很耗效能，而且如果樹越大，資源吃越多。
• 增加查詢效率 : 越大的樹查詢效率一定越小，而不過切小後，『 只對 』那顆小樹查那就快多了。
• 增加表的維護性 : 假設咱們要針對大表中某一段時間砍資料，那這時如果每張表是以時間來分的話，那就只要直接砍掉小表，這樣也不會影響到其它小表的查詢。

上面看似美好。

但分區表不是銀彈

後面章節會說明它的坑處。

MySQL 分區的切分類型

mysql 中分區表提供了以下的類型，然後咱們指定分區的那個欄位都稱為 :

分區鍵

分區鍵的選擇要點提醒

那個欄位會變成每一次查詢時，都會要用的

類型 1 : RANGE 分區

假設咱們由一張儲員工的資料表，那咱們可以選擇以『 RANGE 』來分區，通常這種分區欄位比較常見的有日期或啥有順序的編號之類。

CREATE TABLE users (
    id INT NOT NULL,
    age INT NOT NULL,
)
PARTITION BY RANGE (age) (
    PARTITION p0 VALUES LESS THAN (20),
    PARTITION p1 VALUES LESS THAN (30),
    PARTITION p2 VALUES LESS THAN (40),
    PARTITION p3 VALUES LESS THAN (50)
);

假設咱們有以下的員工 :

A : age = 18
B : age = 25
C : age = 35
D : age = 45
E : age = 15

那這樣上述資料會分別放在不同的分區表中 :

p0 : A,E
p1 : B
p2 : C
p 3 : D

類型 2 : LIST 分區

list 這種分區通常是用在枚舉類型上，如下範例，咱們有一個 type 欄位，然後使用數字來代表類型，然後就使用這個類型來決定分區。

CREATE TABLE users (
    id INT NOT NULL,
    age INT NOT NULL,
    type INT NOT NULL
)
PARTITION BY list (type) (
    PARTITION p0 VALUES LESS THAN (0,1),
    PARTITION p1 VALUES LESS THAN (2),
);

假設咱們有以下的員工 :

A : age = 18, type : 0
B : age = 25, type : 1
C : age = 35, type : 2
D : age = 45, type : 0
E : age = 15, type : 1

那這樣上述資料會分別放在不同的分區表中 :

p0 : A,B,D,E
p1 : E

類型 3 : Hash 分區

它就是會直接將 id 進行 hash 後分成四個區。

CREATE TABLE users (
    id INT NOT NULL,
    age INT NOT NULL,
    type INT NOT NULL
)
PARTITION BY HASH(id)
PARTITIONS 4;

類型 4 : KEY 分區

它就是會直接根據指定的 pk 來分區。

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY, ,
    age INT NOT NULL,
    type INT NOT NULL
)
PARTITION BY KEY()
PARTITIONS 4;

分區使用的注意事項

注意 1 : 查詢時一定有用到分區鍵，不然還是會每個分區找

假設咱們有以下的分區表。

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY, ,
    age INT NOT NULL,
    type INT NOT NULL
)
PARTITION BY KEY()
PARTITIONS 4;

然後咱們下達以下的指令進行 query :

EXPLAIN SELECT * FROM users WHERE age = 10;

那你會看到以下的結果如下 explain 的結果，你會發現它去每一個分區都找過。

partitions 
p0,p1,p2,p2

而如果改成 :

EXPLAIN SELECT * FROM users WHERE id = 1;

結果如下，就有使用到分區表。

partitions 
p0

注意 2 : 沒有使用到分區表查詢會慢

分區別是為了解決單表太大，導致操作太慢所進行的一種解法，以 b+ 樹的概念來看，就是將一顆大樹分成幾個小樹。

但是這有一個問題，如果你的查詢沒有用到分區，那也就代表你需要去每個小樹找，那這樣整體而言 i/o 的次樹會大於一顆大樹。

mysql innodb 是使用 b+ 樹來建立資料，在資料庫中基本上樹的高度大約為 2 至 3，不論資料的多與少，這也代表你分區以後，那幾顆分區後出來的 b+ 樹高度也是會差不多的。

所以有可能大表查詢需要 3 次 i/o，但變到小表可能就只需要 2 次 i/o，但是如果沒有指定分區查詢的話，假設有 4 個小表，那就代表要 2 * 4 = 8 次 i/o 查詢，這樣反而變慢了。

所以這裡記好 :

查詢時一定要包含分區鍵來查詢，沒用到會很慢

注意 3 : NULL 的問題

如果你的分區鍵中，所對應的表格是 null 的話，那他會如何處理呢 ?

它會都儲在第一個分區

所以有時後不知覺第一個分區查詢會非常慢就是這個原因。

注意 4 : 索引的使用問題

分區鍵與索引鍵不相同時

假設咱們有一張表，它們的分區與索引鍵如下 :

欄位 a 與 b

分區鍵 : a
索引鍵 : b

那你想想，如果這個時後下以下的 sql 會如何呢 ? 它基本上會去每一個分區表中的每個顆 b+ 樹去找一次，就算有使用索引 b 仍然會非常的慢，尤其如果是沒有用到『 覆蓋索引 』。

SELECT * FROM table WHERE b = 1;

所以這裡建議，任何查詢一定要配分區鍵

SELECT * FROM table WHERE b = 1 AND a = x

~ 小備註 ~

上面有提到覆蓋索引這東西，如果還不知道的友人們，可以參考看看筆者這篇文章。

30-12 之資料庫層的核心 - MySQL 的索引實現

注意 5 : 在 Range 類型 INSERT 時的問題

在範例類型的分區表的操作時，insert 實際上是進行以下事情 :

分區層鎖住所有分區表，然後決定它是在那，再將它丟入那個分區

事實上這個操作非常的消耗性能，所以基本會建議 range 不要設太多分區。

注意 6 : 記得常用 Explain 來看看有沒有分區

注意 explain 中的 partiation 欄位，如果是全部的分區都有掃到，那就要考慮改一下你的語法。

id: 1
select_type: simple
table: user
partiation: p1

最後的注意 : 分區鍵的選擇非常重要

你只要記好，你只有一次機會選擇它，上線已後要改，會出人命的。

結論與心得

本篇文章中，咱們介紹了淺談了資料庫分區表的概念，它的概念事實上和咱們上一章節介紹的分表非常的相似，但是還是有點不同。

• 分表 : 分表後，應用層需要修改 sql 操作位置，指定到對應的分表上。
• 分區 : 分區後，應用層『 不 』需要修改 sql 操作位置，資料庫層會自動幫你處理。

然後這裡說一下，並不是所有表太大，都需要使用分表或分區這兩個擴展，很多情況下，事實上不需要所有的資料都需要存放在資料庫中，這些資料事實上可以考慮轉移至 s3 或啥的，唯有當資料一定要存在資料庫中，且都需要查詢的情況，才可考慮。

不然分表與分區後，應用端操作是個雷，資料庫端也是個雷。

最後在提醒一下分區與分表是用在 :

單表太大的情境

而不是資料庫太大的情境，我真看過有人不管表大不大，一律都分區這樣玩。

參考資料

• MySQL · 最佳实践 · 分区表基本类型
• MySQL 官網分區
• 【原创】MYSQL 的那些“坑”
• Overview of Partitioning in MySQL-8.0